package net.sppan.blog.controller.admin;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URI;
import java.net.URISyntaxException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.sppan.blog.annotation.RequiresPermissions;
import net.sppan.blog.controller.BaseController;
import net.sppan.blog.model.Offer;
import net.sppan.blog.model.OfferUser;
import net.sppan.blog.model.Role;
import net.sppan.blog.model.User;
import net.sppan.blog.utils.Const;
import net.sppan.blog.utils.OfferStatus;
import net.sppan.blog.utils.OfferType;
import net.sppan.blog.utils.SimpleDateFormatUtil;
import net.sppan.blog.utils.WebUtils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.upload.UploadFile;

@RequiresPermissions(key = { "admin:sys:offer" })
public class AdminOfferController extends BaseController {

	private static final String FORMAT = "第%s行的%s为空或者格式不正确<br/>";
	private static Pattern pattern = Pattern.compile("^[0-9]*\\.*[0-9]*");

	@RequiresPermissions(key = { "admin:sys:offer:list" })
	public void list() {
		String searchId = getPara("search_id");
		String searchStatus = getPara("search_status");
		String searchDate = getPara("search_date");
		String searchOwner = getPara("search_ownername");

		User currentUser = WebUtils.currentUser(this);
		Role role = getSessionAttr("loginUserRole");
		int pageNumber = getParaToInt("pageNumber", 1);
		int pageSize = getParaToInt("pageSize", 10);
		String sql = "";
		if (role.getId() == Const.AdministratorId) {
			sql = " from sys_offer o join sys_offer_user ou on o.id=ou.offer_id where 1=1 ";
			List<User> users = User.dao.findUserByRoleId(Const.AgentId, Const.AdministratorId);
			setAttr("users", users);
		} else {
			sql = " from sys_offer o join sys_offer_user ou on o.id=ou.offer_id where  user=" + currentUser.getId() + " or user='all' ";
		}
		if (!StrKit.isBlank(searchId)) {
			setAttr("search_id", searchId);
			sql += " and o.id=" + searchId;
		}
		if (!StrKit.isBlank(searchStatus) && !searchStatus.equals("0")) {
			setAttr("search_status", searchStatus);
			sql += " and o.status='" + searchStatus + "'";
		}
		if (!StrKit.isBlank(searchOwner) && !searchOwner.equals("0")) {
			setAttr("search_ownername", searchOwner);
			sql += " and o.owner=" + searchOwner;
		}
		if (!StrKit.isBlank(searchDate)) {
			setAttr("search_date", searchDate);
			String[] ds = searchDate.split(" - ");
			String beginDate = ds[0] + " 00:00:00";
			String endDate = ds[1] + " 23:59:59";
			sql += " and o.create_time between '" + beginDate + "' and '" + endDate + "'";
		}
		sql += " order by id desc";
		Page<Offer> page = Offer.dao.paginate(pageNumber, pageSize, "select distinct o.click_num_limit,o.id,o.click_url,o.create_time,o.update_time,o.status,o.owner,o.owner_name,o.offer_type,o.price ", sql);
		setAttr("pageList", page);
		setAttr("loginUser", currentUser);
		setAttr("loginUserRole", role);
		render("list.html");
	}

	/**
	 * 跳转到表单页面
	 */
	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void add_edit() {
		Integer id = getParaToInt("id");
		User currentUser = WebUtils.currentUser(this);
		Role role = getSessionAttr("loginUserRole");
		if (id != null) {
			Offer offer = Offer.dao.findById(id);
			//判断当前offer的所有者，非当前登录用户不允许修改。管理员除外
			if (!role.getName().equals(Const.Administrator) && offer.getOwner().intValue() != currentUser.getId().intValue()) {
				renderJson("no permission");
				return;
			}
			setAttr("offer", offer);
		}
		if (role.getName().equals(Const.Administrator)) {
			List<User> agents = User.dao.findUserByRoleId(Const.AgentId);
			if (id != null) {
				//查询指定投放的user
				List<User> selected = User.dao.findUserByOffer(id);
				if (selected == null) {
					//说明这个offer投放的是all
					setAttr("target_all", "1");
				} else {
					Iterator<User> it = agents.iterator();
					while (it.hasNext()) {
						User _user = it.next();
						for (User u : selected) {
							if (u.getId().intValue() == _user.getId().intValue()) {
								it.remove();
							}
						}
					}
					setAttr("target_user", selected);
				}
			}

			setAttr("agents", agents);
			render("admin_form.html");
		} else {
			render("form.html");
		}
	}

	/** 
	 * 新增-更新offer
	 */
	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void save_update() {
		String targetUsers = getRequest().getParameter("target_user");
		Offer offer = getModel(Offer.class);
		//判断当前offer的所有者，非当前登录用户不允许修改。管理员除外
		User currentUser = WebUtils.currentUser(this);
		Role role = getSessionAttr("loginUserRole");
		if (role.getName().equals(Const.Agent)) {
			targetUsers = currentUser.getId().toString();
		}
		//判断是否有重复记录owner+click唯一
		Offer wo = Offer.dao.findFirst("select * from sys_offer where owner=" + currentUser.getId() + " and click_url='" + offer.getClickUrl() + "'");
		if (wo != null) {
			renderJson(false, "offer already exists");
			return;
		}
		if (offer.getId() == null) {
			offer.setCreateTime(new Date());
			offer.setOwner(currentUser.getId());
			offer.setOwnerName(currentUser.getName());
			offer.setOfferType("cpc");
		}
		if (!role.getName().equals(Const.Administrator) && offer.getOwner().intValue() != currentUser.getId().intValue()) {
			renderJson("no permission");
			return;
		}
		if (offer.getId() == null) {
			boolean success = offer.save();
			//保存offer可投放范围
			String[] strs = targetUsers.split(",");
			if (strs != null && strs.length > 0) {
				for (String target : strs) {
					OfferUser ou = new OfferUser();
					ou.setOfferId(offer.getId());
					ou.setCreateTime(new Date());
					ou.setUser(target);
					ou.save();
				}
			}
			renderJson(success, "added successfully");
		} else {
			offer.setUpdateTime(new Date());
			if (role.getName().equals(Const.Administrator)) {
				OfferUser.dao.updateOfferByUser(targetUsers, offer.getId());
			}
			renderJson(offer.update(), "modify successfully");
		}
	}

	/**
	 * 删除offer
	 */
	@RequiresPermissions(key = { "admin:sys:offer:delete" })
	public void delete() {
		final Integer id = getParaToInt("id");
		Offer offer = Offer.dao.findById(id);
		//判断当前offer的所有者，非当前登录用户不允许删除。管理员除外
		User currentUser = WebUtils.currentUser(this);
		Role role = getSessionAttr("loginUserRole");
		if (!role.getName().equals(Const.Administrator) && offer.getOwner().intValue() != currentUser.getId().intValue()) {
			renderJson("no permission");
			return;
		}
		boolean isSuccess = Db.tx(new IAtom() {
			@Override
			public boolean run() {
				try {
					Offer.dao.deleteById(id);
					//删除offer和user的关系
					Db.update("delete from sys_offer_user where offer_id=" + id);
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		});
		if (isSuccess) {
			renderJson(isSuccess, "delete success");
		} else {
			renderJson(isSuccess, "delete failed");
		}

	}

	/**
	 * 切换状态
	 */
	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void switchStatus() {
		Integer id = getParaToInt("id");
		String status = getPara("status");
		String s = "";
		if (status.equals(OfferStatus.published.name())) {
			s = OfferStatus.suspend.name();
			boolean isSuccess = Offer.dao.switchStatus(id, s);
			if (isSuccess) {
				renderJson(isSuccess, "suspend success");
			} else {
				renderJson(isSuccess, "suspend failed");
			}
		} else {
			s = OfferStatus.published.name();
			boolean isSuccess = Offer.dao.switchStatus(id, s);
			if (isSuccess) {
				renderJson(isSuccess, "publish success");
			} else {
				renderJson(isSuccess, "publish failed");
			}
		}
	}

	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void importExcel() {
		List<User> agents = User.dao.findUserByRoleId(Const.AgentId);
		setAttr("agents", agents);
		Role role = getSessionAttr("loginUserRole");
		if (role.getId() == Const.AdministratorId) {
			setAttr("admin", "1");
		}
		render("importExcel.html");
	}

	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void downfile() {//文件下载
		URI path = null;
		try {
			path = this.getClass().getClassLoader().getResource("offer-template.xlsx").toURI();
		} catch (URISyntaxException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		File file = new File(path);
		if (file.exists()) { //如果文件存在
			renderFile(file);
		} else {
			renderJson();
		}
	}

	@Before(Tx.class)
	@RequiresPermissions(key = { "admin:sys:offer:save" })
	public void upload() {
		User user = WebUtils.currentUser(this);
		StringBuilder sb = new StringBuilder("");
		UploadFile uploadFile = this.getFile("file");
		File file = uploadFile.getFile();
		final String targetUser = getPara("target_user");
		final List<Offer> offers = new ArrayList<>();
		try {
			InputStream is = new FileInputStream(file);
			Workbook hssfWorkbook = null;
			if (isExcel2007(file.getName())) {
				hssfWorkbook = new XSSFWorkbook(is);
			} else {
				hssfWorkbook = new HSSFWorkbook(is);
			}
			// 循环工作表Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				// 循环行Row
				for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					Row hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow == null) {
						continue;
					}
					// 循环列Cell
					Offer offer = new Offer();
					String clickUrl = getValue(hssfRow.getCell(0));
					if (StringUtils.isEmpty(clickUrl)) {
						sb.append(String.format(FORMAT, rowNum + 1, "click_url"));
					} else {
						offer.setClickUrl(clickUrl);
					}
					String price = getValue(hssfRow.getCell(1));
					Matcher m = pattern.matcher(price);
					if (!m.matches()) {
						sb.append(String.format(FORMAT, rowNum + 1, "price"));
					} else {
						offer.setPrice(price);
					}
					String offerType = getValue(hssfRow.getCell(2));
					if (StringUtils.isNotEmpty(offerType) && OfferType.getCode(offerType).equals("-1")) {
						sb.append(String.format(FORMAT, rowNum + 1, "offer_type"));
					} else {
						offer.setOfferType(offerType == null ? "cpc" : offerType);
					}
					String clickNumLimit = getValue(hssfRow.getCell(3));
					if (StringUtils.isEmpty(clickNumLimit)) {
						sb.append(String.format(FORMAT, rowNum + 1, "click_num_limit"));
					} else {
						offer.setClickNumLimit(Double.valueOf(clickNumLimit).intValue());
					}
					offer.setCreateTime(new Date());
					offers.add(offer);
				}
			}
			file.delete();
			if (offers.size() == 0) {
				renderJson(false, "excel is empty！");
				return;
			}
			if (StringUtils.isNotEmpty(sb.toString())) {
				renderJson(false, sb.toString());
				return;
			} else {
				//再判断是否有重复的记录
				Map<String, List<Offer>> map = new HashMap<>();
				for (Offer offer : offers) {
					String key = offer.getClickUrl();
					List<Offer> os = map.get(key);
					if (os == null) {
						os = new ArrayList<>();
					}
					os.add(offer);
					if (os.size() > 1) {
						sb.append(key + ":excel中存在重复数据<br/>");
					}
					map.put(key, os);
				}
				if (StringUtils.isEmpty(sb.toString())) {
					final List<OfferUser> ous = new ArrayList<>();
					//判断数据库中是否存在
					for (Offer offer : offers) {
						offer.setOwner(user.getId());
						offer.setOwnerName(user.getName());
						Offer wo = Offer.dao.findFirst("select * from sys_offer where owner=" + user.getId() + " and click_url='" + offer.getClickUrl() + "'");
						if (wo != null) {
							String key = "owner=" + user.getName() + "," + "click_url=" + offer.getClickUrl();
							sb.append(key + ":数据库中存在重复数据<br/>");
						}
					}
					if (StringUtils.isEmpty(sb.toString())) {

						boolean b = Db.tx(new IAtom() {
							@Override
							public boolean run() {
								try {
									for (Offer offer : offers) {
										offer.save();
									}
									//Db.batchSave(offers, offers.size());
									return true;
								} catch (Exception e) {
									e.printStackTrace();
									return false;
								}
							}
						});

						boolean c = Db.tx(new IAtom() {
							@Override
							public boolean run() {
								try {
									for (Offer offer : offers) {
										String[] strs = targetUser.split(",");
										if (strs != null && strs.length > 0) {
											for (String target : strs) {
												OfferUser ou = new OfferUser();
												ou.setOfferId(offer.getId());
												ou.setCreateTime(new Date());
												ou.setUser(target);
												ous.add(ou);
											}
										}
									}
									Db.batchSave(ous, ous.size());
									return true;
								} catch (Exception e) {
									e.printStackTrace();
									return false;
								}
							}
						});
						if (b) {
							renderJson(true, "导入成功！");
						} else {
							renderJson(false, "导入失败！");
						}
					} else {
						renderJson(false, sb.toString());
					}
				} else {
					renderJson(false, sb.toString());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			renderJson(false, "导入失败");
			return;
		}
	}

	public boolean isExcel2007(String filePath) {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}

	private String getValue(Cell hssfCell) {
		if (hssfCell == null) {
			return "";
		}
		if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
				return SimpleDateFormatUtil.format(date, SimpleDateFormatUtil.ymd);
			} else {
				return String.valueOf(hssfCell.getNumericCellValue());
			}
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}

	}
}
